package com.lysj.bhp.borrowing.repository;

import com.lysj.bhp.approve.domain.ApproveRecord;
import com.lysj.bhp.blacklist.domain.Blacklist;
import com.lysj.bhp.borrowing.domain.BorrowingInstallment;
import com.lysj.bhp.borrowing.vo.OverdueUserVo;
import com.lysj.bhp.borrowing.vo.OverdueVo;
import com.lysj.bhp.customer.vo.OverdueInstallmentVo;
import com.lysj.bhp.customer.vo.OverdueListVo;
import com.lysj.bhp.user.domain.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

/**
 * @Author: ZhangWenJian
 * @Create: 2018/8/17--17:50
 * @Description:
 */
public interface BorrowingInstallmentRepository extends JpaRepository<BorrowingInstallment, Long>, JpaSpecificationExecutor {
    List<BorrowingInstallment> findByBorrowingRecordIdOrderByNperAsc(Long borrowingRecordId);

    //查找时间大于指定时间，未还本期记录的用户。
    @Query("SELECT a from BorrowingInstallment a where a.actualRepaymentDate = null and a.endRepaymentDate < ?1 order by a.userId asc ,a.borrowingRecordId desc,a.endRepaymentDate desc")
    List<BorrowingInstallment> findOverdueRecoed(Date endRepaymentDate);

    //查询该时间违约的用户
    @Query("SELECT new java.lang.Long(a.userId) from BorrowingInstallment a where a.actualRepaymentDate = null and a.endRepaymentDate < ?1 GROUP BY a.userId")
    List<Long> findOverdurUser(Date endRepaymentDate);

    List<BorrowingInstallment> findByUserIdAndBorrowingRecordId(Long userId, Long borrowingRecordId);

    BorrowingInstallment findByUserIdAndBorrowingRecordIdAndNper(Long userId, Long borrowingRecordId, Integer nper);

    //查询本日应该还款的用户
    @Query("SELECT a from BorrowingInstallment a where  a.endRepaymentDate = ?1 and a.actualRepaymentDate = null order by a.userId")
    List<BorrowingInstallment> notifyingTheUsers(Date endRepaymentDate);

    //查询该用户当前逾期降序
    List<BorrowingInstallment> findByUserIdAndEndRepaymentDateLessThanAndActualRepaymentDateNullOrderByEndRepaymentDateDesc(Long userId,Date nowDate);

    //查询该用户当前逾期升序
    List<BorrowingInstallment> findByUserIdAndActualRepaymentDateNullOrderByEndRepaymentDateAsc(Long userId);

    //查询该用户是否借过款
    List<BorrowingInstallment> findByUserId(Long userId);

    @Query("SELECT new java.lang.Long(count(a.userId)) from BorrowingInstallment a where a.userId = ?1")
    Long borrowingInstallmentRow(Long userId);

    int countByUserIdAndBorrowingRecordIdAndRepayStatus(Long userId, Long borrowingRecordId, Integer repayStatus);

    //查询当前之前
    List<BorrowingInstallment> findByBorrowingRecordIdAndNperLessThanEqual(Long borrowingRecordId,Integer nper);

    //查询当前时间逾期的用户
    @Query("select new com.lysj.bhp.customer.vo.OverdueListVo (a.userId,b.email,b.phone,min(a.endRepaymentDate),count (a.userId)) from BorrowingInstallment a,User b where a.userId = b.userId and a.endRepaymentDate < ?1 and a.actualRepaymentDate = null GROUP BY a.userId")
    List<OverdueListVo> findOverdueListRecord(Date nowDate);

    @Query("select new com.lysj.bhp.customer.vo.OverdueInstallmentVo(a.nper,a.endRepaymentDate,a.returnMoney,a.returnDividend,a.actualRepaymentMoney,a.defaultMoney,b.borrowingType,b.borrowingOrderNo)from BorrowingInstallment a,BorrowingRecord b where a.borrowingRecordId = b.borrowingRecordId and a.userId = ?1 and a.endRepaymentDate < ?2 and a.actualRepaymentDate = null ORDER BY a.endRepaymentDate")
    List<OverdueInstallmentVo> findOverdueInstallmentVo(Long userId,Date nowDate);


    List<BorrowingInstallment> findByBorrowingRecordId(Long borrowingRecordId);

    //查找当前时间逾期大于4天的该用户的逾期记录
    @Query("select a from BorrowingInstallment a,BorrowingRecord b where a.borrowingRecordId = b.borrowingRecordId and a.userId = ?1 and a.endRepaymentDate <= ?2 and a.actualRepaymentDate = null GROUP BY a.borrowingRecordId ORDER BY b.mortgageType")
    List<BorrowingInstallment> getFiltrateOverdueRecord(Long userId,Date endEndRepaymentDate);
}
